﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using System.Diagnostics;

namespace Mengsk.ERP.Database
{
    public class DBHelper
    {
        private const string CONNECTION_STRING = "Server=1008-mosige;Database=MengskERP;User Id=sa;Password=sa12345";
        private static DBHelper instance = new DBHelper();
        private SqlConnection sqlConnection = null;

        public static DBHelper Instance { get { return instance; } }

        public SqlConnection MakeConnection()
        {
            if (this.sqlConnection == null)
            {
                this.sqlConnection = new SqlConnection(CONNECTION_STRING);
            }

            if (this.sqlConnection.State != System.Data.ConnectionState.Open)
            {
                this.sqlConnection.Open();
            }

            return this.sqlConnection;
        }

        public static PropertyInfo[] GetPropertyInfos(Type type, bool isInclude, params string[] proerptyNames)
        {
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.Public).Where(pi => pi.CanRead && pi.CanWrite).ToArray();
            PropertyInfo[] newPis = pis.Where(pi => proerptyNames.Contains(pi.Name) == isInclude).ToArray();
            return newPis;
        }

        public static PropertyInfo[] GetPropertyInfosWith(Type type, params string[] propertyNames)
        {
            return GetPropertyInfos(type, true, propertyNames);
        }

        public static PropertyInfo[] GetPropertyInfosWithout(Type type, params string[] propertyNames)
        {
            return GetPropertyInfos(type, false, propertyNames);
        }

        public void FillIDataReaderToObject(IDataReader reader, object obj, PropertyInfo[] selectedProperties)
        {
            foreach (PropertyInfo pi in selectedProperties)
            {
                try
                {
                    pi.SetValue(obj, reader[pi.Name], null);
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                }
            }
        }

        private object GetValue(object obj, PropertyInfo pi)
        {
            object value = pi.GetValue(obj, null);

            if (value == null)
            {
                throw new Exception("对象属性:" + pi.Name + "的值为空");
            }

            //如果是Guid则，返回字符表达形式
            if (value is Guid)
            {
                return value.ToString();
            }

            if (value is string)
            {
                return value.ToString().Trim();
            }

            if (value is Enum)
            {
                return (int)value;
            }

            if (value is DateTime)
            {
                DateTime da = (DateTime)value;
                if (da == DateTime.MinValue)
                {
                    return System.Data.SqlTypes.SqlDateTime.MinValue;
                }
            }

            return value;
        }


        private SqlParameter[] ConvertObjectToParameters(object obj, bool isInclude, params string[] proerptyNames)
        {
            PropertyInfo[] pis = GetPropertyInfos(obj.GetType(), isInclude, proerptyNames);
            SqlParameter[] pas = pis.Select(pi => new SqlParameter("@" + pi.Name, GetValue(obj, pi))).ToArray();
            return pas;
        }


        public T[] Select<T>(string db, string table, string where, SqlParameter[] whereParas, bool isInclude, params string[] propertyNames) where T : new()
        {
            List<T> items = new List<T>();
            string sqlQuery = string.Format("select * from [{0}]{1}", table, string.IsNullOrWhiteSpace(where) ? "" : " where " + where);
            SqlConnection connection = this.MakeConnection();
            SqlCommand cmd = new SqlCommand(sqlQuery, sqlConnection);
            IDataReader reader = null;
            PropertyInfo[] pis = GetPropertyInfos(typeof(T), isInclude, propertyNames);
            try
            {
                if (whereParas != null && whereParas.Length > 0)
                {
                    cmd.Parameters.AddRange(whereParas.ToArray());
                }
                reader = cmd.ExecuteReader(CommandBehavior.Default);
                while (reader.Read())
                {
                    T obj = new T();
                    this.FillIDataReaderToObject(reader, obj, pis);
                    items.Add(obj);
                }
            }
            finally
            {
                if (reader != null && reader.IsClosed == false)
                {
                    reader.Close();
                }
            }

            return items.ToArray();
        }

        public T[] SelectEx<T>(string db, string sqlQuery, SqlParameter[] whereParas, bool isInclude, params string[] propertyNames) where T : new()
        {
            List<T> items = new List<T>();
            SqlConnection connection = this.MakeConnection();
            SqlCommand cmd = new SqlCommand(sqlQuery, sqlConnection);
            IDataReader reader = null;
            PropertyInfo[] pis = GetPropertyInfos(typeof(T), isInclude, propertyNames);
            try
            {
                if (whereParas != null && whereParas.Length > 0)
                {
                    cmd.Parameters.AddRange(whereParas.ToArray());
                }
                reader = cmd.ExecuteReader(CommandBehavior.Default);
                while (reader.Read())
                {
                    T obj = new T();
                    this.FillIDataReaderToObject(reader, obj, pis);
                    items.Add(obj);
                }
            }
            finally
            {
                if (reader != null && reader.IsClosed == false)
                {
                    reader.Close();
                }
            }

            return items.ToArray();
        }

        public int Insert(string db, string table, object obj, bool isInclude, params string[] propertyNames)
        {
            PropertyInfo[] pis = GetPropertyInfos(obj.GetType(), isInclude, propertyNames);
            string[] pisNames = pis.Select(pi => string.Format("[{0}]", pi.Name)).ToArray();
            string[] pisNamesWithAt = pis.Select(pi => "@" + pi.Name).ToArray();
            string sqlQuery = string.Format("insert into [dbo].[{0}]({1}) values({2})", table, string.Join(",", pisNames), string.Join(",", pisNamesWithAt));
            SqlParameter[] para = this.ConvertObjectToParameters(obj, isInclude, propertyNames);
            SqlCommand cmd = new SqlCommand(sqlQuery, MakeConnection());
            cmd.Parameters.AddRange(para);
            return cmd.ExecuteNonQuery();
        }

        public int Update(string db, string table, string where, IEnumerable<SqlParameter> whereParameters, object value, bool isInclude, params string[] propertyNames)
        {
            PropertyInfo[] pis = GetPropertyInfos(value.GetType(), isInclude, propertyNames);
            SqlParameter[] paras = ConvertObjectToParameters(value, isInclude, propertyNames);
            string sqlText = string.Format("update [{0}] set {1} where {2}", table, string.Join(",", pis.Select(pi => string.Format("[{0}]=@{0}", pi.Name))), where);
            SqlCommand cmd = new SqlCommand(sqlText, MakeConnection());
            if (whereParameters != null && whereParameters.Count() > 0)
            {
                cmd.Parameters.AddRange(whereParameters.ToArray());
            }
            cmd.Parameters.AddRange(paras);
            return cmd.ExecuteNonQuery();
        }

        public int Delete(string db, string table, string where, params SqlParameter[] parameters)
        {
            string sqlText = string.Format("delete from [{0}] where {1}", table, where);
            SqlCommand cmd = new SqlCommand(sqlText, MakeConnection());
            if (parameters != null && parameters.Count() > 0)
            {
                cmd.Parameters.AddRange(parameters.ToArray());
            }
            return cmd.ExecuteNonQuery();
        }
    }
}
